Skip to main content

Create Datasource with Hsqldb

Introduction Hsqldb​

This document contains information about the Hsqldb database how to install and connect with OPNBI.This document has step-by-step information on how to install, connect Hsqldb Database with OPNBI and will show one use case example.

HyperSQL Database (HSQLDB) is a modern relational database manager that conforms closely to the SQL:2011 standard and JDBC 4 specifications. It supports all core features and RDBMS. HSQLDB is used for development, testing, and deployment of database applications.HSQLDB is also suitable for business intelligence, ETL, and other applications that process large data sets.

Objective​

Objective of this document is to connect Hsqldb Database with OPNBI.

Prerequisites​

  1. Hsqldb Jar

  2. Java Eclipse

  3. Hsqldb Database

Skill Level Required​

Basic knowledge of Database.

1. Download Hsqldb JDBC driver:​

To get the JDBC driver, Go the vendor's end and download the (Hsqldb-connector-java.jar) Driver file from here as shown in figure below:

Docusaurus Slash Introduction

Go to the folder where you have downloaded this file and extract the zip file.

When you go inside the folder you will be able to see lib folder.

copy the hsqldb & hsqldb-jdk8 jar file and add it into the OPNBI.

Note:

  • To add jar go to the Directory where OPNBI installed OPNBI\apache-tomcat-8.5.61\webapps\OPNBI\WEB-INF\lib folder and add the .jar file here.

    OPNBI: OPNBI is folder name where OPNBI is installed.

    Apache-tomcat-8.5.61: Apache Server [Apache folder is located inside the OPNBI folder, suffix version may change]

Restart the Tomcat server after adding a jar in the lib folder.

2. Download the java Eclipse from here.​

Follow steps given in Video link in order to Install the Eclipse Click here to open link

Open the Eclipse and click on open Perspective from the top right corner,as shown in figure below:

Docusaurus Slash Introduction

Select the Database Development and click on Open button.

Now, right click on the Database Connection and click on New, as shown in figure below:

Docusaurus Slash Introduction

It will display the Connection Profile in that select the HSQLDB and Give name.

Click on New Driver Definition and choose HSQLDB JDBC driver, as shown in figure below:

Docusaurus Slash Introduction

Now go to the Jar List and select the Driver files click on edit this jar file is located at HSQLDb >> Lib Folder.

Click on Open and Ok.

It will redirect you on Specify Driver and Connection Details Page, as shown in figure below:

Docusaurus Slash Introduction

Insert the following data in the connection Dialog box:

Note:

Database Name: test [ Add name as per your requirements]

Database location: [Give path where HSQLDB database is located]

Username:sa [Provide the username of your requirements]

Enter Password: [Enter your respective password]

Click on Test Connection Button and it will display message Ping Succeeded, as shown in figure below:

Click on Finish and Close the Eclipse.

Connecting hsqldb Datasource with OPNBI​

Go to the Master data section and click on Data Source, as shown in the figure below:

Docusaurus Slash Introduction

Click on Create button from the bottom horizontal menu bar from Datasource section, as shown in the figure below:

Docusaurus Slash Introduction

The Create Datasource window will open, as shown in the figure below:

Docusaurus Slash Introduction

Search for Hsqldb data source from Top-right search bar in Datasource section, as shown in the figure below:

Docusaurus Slash Introduction

Click on Hsqldb to create a data source using SQLite.

Docusaurus Slash Introduction

As you click on Hsqldb the create dialog box will pop-up on screen, as shown in the figure below:

Docusaurus Slash Introduction

Wait Time: Wait Time Analysis for Database Optimization. Wait time analysis helps you understand how much time each SQL statement is spending across all its executions in a given period of time.

Is JNDI: Java Naming and Directory Interface is the name of the interface in the Java programming language. It is an API( Application Program Interface) that works with servers and can fetch files from a database using naming conventions.

Class: Database class Contains methods for creating and manipulating data.

Connection URL: A database connection URL is a string that your DBMS JDBC driver uses to connect to a database.It can contain information such as where to search for the database, the name of the database to connect to, and configuration properties.

Username: Username is the name of the user that is connected to the database.

Password: You can set a password on an Access database to prevent unauthorized access to the database file.

Extra Configuration: Using Extra Configuration you can add extra fields as per ypur requirements.

Insert the following data in the Datasource connection Dialog box:

Note:

Enter Display Name: Hsqldb [ Add name as per your requirements]

Enter Class:org.hsqldb.jdbcDriver

Enter URL: jdbc:hsqldb:/database_file_path [Add Connection URL as per your connection]

(URL: jdbc:hsqldb:C:\Hsqldb\hsqldb-2.6.1\hsqldb) Datasource

Enter Username: sa [Provide the username of your SQLite Datasource]

Enter Password: [Enter your respective SQLite password]

Click on Test Connection Button to Check Database Connection, as shown in figure below:

Docusaurus Slash Introduction

After Connection Successful, Click on ok and save button to close the Dialog box.

Docusaurus Slash Introduction

To create a dataset Select the Hsqldb DataSource and click on Create Dataset, as shown in the figure below:

Docusaurus Slash Introduction

When you click on create Dataset the following screen will be display.

Docusaurus Slash Introduction

Enter the Following details in Dataset.

Name: Hsqldb (Add name as per your requirements)

Source: Hsqldb (select the data source which you have created)

DataSource: JDBC

Docusaurus Slash Introduction